﻿Imports System.IO
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Public Class gOpenXml
    Public Shared Function gExcelNew(ByVal SheetName As String) As MemoryStream '新建Excel
        Dim st As New MemoryStream
        Using spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Create(st, SpreadsheetDocumentType.Workbook)
            Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart
            workbookpart.Workbook = New Workbook
            Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)
            Dim wbsp As WorkbookStylesPart = workbookpart.AddNewPart(Of WorkbookStylesPart)
            wbsp.Stylesheet = New Stylesheet
            wbsp.Stylesheet.Fonts = New Fonts(New Font(New FontName With {.Val = "宋体"}, New FontSize With {.Val = 10}))
            worksheetPart.Worksheet = New Worksheet(New SheetData)
            Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(New Sheets)
            Dim sheet As Sheet = New Sheet
            sheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
            sheet.SheetId = 1
            sheet.Name = SheetName
            sheets.Append(sheet)
            workbookpart.Workbook.Save()
        End Using
        Return st
    End Function
    Public Shared Function gSheetNew(ByVal workbookPart As WorkbookPart, ByVal SheetName As String) As WorksheetPart '添加新工作表
        Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)
        newWorksheetPart.Worksheet = New Worksheet(New SheetData)
        newWorksheetPart.Worksheet.Save()
        Dim sheets As Sheets = workbookPart.Workbook.GetFirstChild(Of Sheets)
        Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)
        Dim sheetId As UInteger = 1
        If (sheets.Elements(Of Sheet).Count() > 0) Then
            sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max + 1
        End If
        Dim sheet As Sheet = New Sheet
        sheet.Id = relationshipId
        sheet.SheetId = sheetId
        sheet.Name = SheetName
        sheets.Append(sheet)
        workbookPart.Workbook.Save()
        Return newWorksheetPart
    End Function
    Public Shared Function gExcelRead(ByVal st As MemoryStream) As DataSet '读取Excel
        Dim ds As New DataSet
        Using spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Open(st, False)
            Dim workbookPart As WorkbookPart = spreadsheetDocument.WorkbookPart
            Dim wsp As IEnumerable(Of WorksheetPart) = workbookPart.WorksheetParts.AsEnumerable
            Dim stringTable = workbookPart.GetPartsOfType(Of SharedStringTablePart).FirstOrDefault()
            Dim str As String = ""
            For a = 0 To wsp.Count - 1
                Dim shd As SheetData = wsp(a).Worksheet.Elements(Of SheetData).First
                Dim dt As New DataTable, iscol As Boolean = True, dtstr As String() = {}, czs As Integer = 0
                For Each r As Row In shd.Elements(Of Row)
                    If czs < r.Elements.Count - 1 Then
                        czs = r.Elements.Count - 1
                    End If
                    ReDim dtstr(czs)
                    Dim idx As Integer = -1
                    For Each c As Cell In r.Elements(Of Cell)
                        If c IsNot Nothing Then
                            str = c.InnerText
                            If c.DataType IsNot Nothing Then
                                If str = "" Then
                                    str = Nothing
                                Else
                                    Select Case c.DataType.Value
                                        Case CellValues.SharedString
                                            If stringTable IsNot Nothing Then
                                                str = stringTable.SharedStringTable.ElementAt(Integer.Parse(str)).InnerText
                                            End If
                                        Case CellValues.Boolean
                                            Select Case str
                                                Case "0"
                                                    str = "FALSE"
                                                Case Else
                                                    str = "TRUE"
                                            End Select
                                    End Select
                                End If
                            End If
                        End If
                        If iscol Then
                            dt.Columns.Add(str)
                        Else
                            If c.CellReference IsNot Nothing Then
                                Dim gl As Byte() = System.Text.Encoding.UTF8.GetBytes(c.CellReference.Value)
                                Dim sy As Integer = 0
                                For i = 0 To gl.Length - 1
                                    If gl(i) > 64 Then
                                        Select Case i
                                            Case 0
                                                sy = gl(i) - 65
                                            Case 1
                                                sy = gl(i) - 65 + 26
                                            Case 2
                                                sy = gl(i) - 65 + 26 * 26
                                        End Select
                                    Else
                                        Exit For
                                    End If
                                Next
                                dtstr(sy) = str
                            Else
                                idx += 1
                                dtstr(idx) = str
                            End If
                        End If
                    Next
                    If iscol Then
                        iscol = False
                    Else
                        dt.Rows.Add(dtstr)
                    End If
                Next
                ds.Tables.Add(dt)
            Next
        End Using
        Return ds
    End Function
    Public Shared Sub gSheetData(ByVal worksheetPart As WorksheetPart, ByVal dt As DataTable, ByVal dl As String()) '写入数据,dl新加的一行表头
        Dim worksheet As Worksheet = worksheetPart.Worksheet
        Dim sd As SheetData = worksheet.GetFirstChild(Of SheetData)
        If dl IsNot Nothing Then '自定义表头，双表头用
            Dim rl As New Row
            For i = 0 To dl.Length - 1
                rl.Append(New Cell() With {.CellValue = New CellValue(dl(i)), .DataType = New EnumValue(Of CellValues)(CellValues.String)})
            Next
            sd.AppendChild(rl)
        End If
        Dim r As New Row
        For i = 0 To dt.Columns.Count - 1
            r.Append(New Cell() With {.CellValue = New CellValue(dt.Columns.Item(i).ToString), .DataType = New EnumValue(Of CellValues)(CellValues.String)})
        Next
        sd.AppendChild(r)
        For i = 0 To dt.Rows.Count - 1
            Dim r1 As New Row
            For j = 0 To dt.Columns.Count - 1
                Dim dstr As String = dt.Rows(i).Item(j).GetType.ToString
                If dstr = "System.Decimal" Or dstr = "System.Int16" Or dstr = "System.Int32" Or dstr = "System.Int64" Then
                    r1.Append(New Cell() With {.CellValue = New CellValue(dt.Rows(i).Item(j).ToString), .DataType = New EnumValue(Of CellValues)(CellValues.Number)})
                Else
                    dstr = dt.Rows(i).Item(j).ToString
                    If dt.Rows(i).Item(j).GetType.ToString = "System.DBNull" Then dstr = Nothing
                    r1.Append(New Cell() With {.CellValue = New CellValue(dstr), .DataType = New EnumValue(Of CellValues)(CellValues.String)})
                End If
            Next
            sd.AppendChild(r1)
        Next
        worksheet.Save()
    End Sub
    Shared Function gExcelOut(ByVal tbn As String, ByVal dt As DataTable) As MemoryStream 'Excel数据生成
        Dim st As MemoryStream = gExcelNew(tbn)
        Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(st, True)
            gSheetData(spreadSheet.WorkbookPart.WorksheetParts.First, dt, Nothing)
            spreadSheet.WorkbookPart.Workbook.Save()
        End Using
        Return st
    End Function
    Shared Function gExcelOut(ByVal tbn As String, ByVal dt As DataTable, ByVal dl As String()) As MemoryStream 'Excel数据生成新加一行表头
        Dim st As MemoryStream = gExcelNew(tbn)
        Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(st, True)
            gSheetData(spreadSheet.WorkbookPart.WorksheetParts.First, dt, dl)
            spreadSheet.WorkbookPart.Workbook.Save()
        End Using
        Return st
    End Function
    Shared Function gExcelOuts(ByVal ds As DataSet, ByVal sname As String()) As MemoryStream 'Excel数据生成分表
        Dim st As MemoryStream = gExcelNew(sname(0) & "-" & "1")
        Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(st, True)
            gSheetData(spreadSheet.WorkbookPart.WorksheetParts.First, ds.Tables(0), Nothing)
            For i = 1 To ds.Tables.Count - 1
                Dim wsp As WorksheetPart = gSheetNew(spreadSheet.WorkbookPart, sname(i) & "-" & (i + 1).ToString)
                gSheetData(wsp, ds.Tables(i), Nothing)
            Next
            spreadSheet.WorkbookPart.Workbook.Save()
        End Using
        Return st
    End Function
    Shared Function gExcelOuts(ByVal ds As DataSet, ByVal sname As String(), ByVal dl As String()) As MemoryStream 'Excel数据生成分表新加一行表头
        Dim st As MemoryStream = gExcelNew(sname(0) & "-" & "1")
        Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(st, True)
            gSheetData(spreadSheet.WorkbookPart.WorksheetParts.First, ds.Tables(0), dl)
            For i = 1 To ds.Tables.Count - 1
                Dim wsp As WorksheetPart = gSheetNew(spreadSheet.WorkbookPart, sname(i) & "-" & (i + 1).ToString)
                gSheetData(wsp, ds.Tables(i), dl)
            Next
            spreadSheet.WorkbookPart.Workbook.Save()
        End Using
        Return st
    End Function
End Class